package com.fact.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.fact.dto.SegmentDTO;
import com.fact.utils.connection.ConnectionManager;

public class SegmentService {
	
	public ArrayList<SegmentDTO> getSegmentList()
	{
		SegmentDTO segment=null;
		ArrayList<SegmentDTO> segmentList=new ArrayList<SegmentDTO>();
		Connection conn = ConnectionManager.getConnection();
		String sql="Select * from MST_SEGMENTS order by SEGMENT_NAME";
		   
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				r = stmt.executeQuery();
				while (r.next())
				{
					segment=new SegmentDTO();
					segment.setSegmentId(r.getInt("SEGMENT_ID"));
					segment.setSegmentName(r.getString("SEGMENT_NAME"));
					segment.setPosition(r.getInt("POSITION"));
					segmentList.add(segment);
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		return segmentList;
	}
	public boolean saveSegment(SegmentDTO segment)
	{

		Connection conn = ConnectionManager.getConnection();
		String sql=" Insert into MST_SEGMENTS(SEGMENT_ID,SEGMENT_NAME,POSITION) " +
				   " values(SQN_SEGMENT.nextval,?,?)";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,segment.getSegmentName());
				stmt.setInt(2,segment.getPosition());
				
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 	if(response==1)
	 		return true;
	 	else
	 		return false;
	}
	
	public boolean deleteSegment(int segmentId)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Delete MST_SEGMENTS Where SEGMENT_ID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1,segmentId);
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	
	public boolean editSegment(SegmentDTO segment)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Update MST_SEGMENTS  set SEGMENT_NAME=?,POSITION=?  Where SEGMENT_ID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setString(1,segment.getSegmentName());
				stmt.setInt(2,segment.getPosition());
				stmt.setInt(3,segment.getSegmentId());
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	
	public SegmentDTO getSegment(int segmentId)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Select * MST_SEGMENTS  Where SEGMENT_ID=?";
		PreparedStatement stmt = null;
		SegmentDTO  segment=null;
		ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setInt(1,segmentId);
				r = stmt.executeQuery();
				if (r.next())
				{
					segment=new SegmentDTO();
					segment.setSegmentId(r.getInt("SEGMENT_ID"));
					segment.setSegmentName(r.getString("SEGMENT_NAME"));
					segment.setPosition(r.getInt("POSITION"));
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 		return segment;
	}
	
	public SegmentDTO getSegment(String segmentname)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Select * MST_SEGMENTS  Where lower(SEGMENT_NAME)=lower(?)";
		PreparedStatement stmt = null;
		SegmentDTO  segment=null;
		ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setString(1,segmentname);
				r = stmt.executeQuery();
				if (r.next())
				{
					segment=new SegmentDTO();
					segment.setSegmentId(r.getInt("SEGMENT_ID"));
					segment.setSegmentName(r.getString("SEGMENT_NAME"));
					segment.setPosition(r.getInt("POSITION"));
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 		return segment;
	}

}
